#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import pandas as pd
import numpy as np
import os
from typing import List, Set

def analyze_empty_authors():
    """分析Excel文件中通讯作者为空、NULL或NA的行"""
    
    # Excel文件路径
    excel_file = r"C:\Users\byan\Desktop\crawl_by_trae\crawl_by_trae\data\crawl_sciencedirect_results.xlsx"
    
    # 检查文件是否存在
    if not os.path.exists(excel_file):
        print(f"错误：文件不存在 {excel_file}")
        return
    
    try:
        # 读取Excel文件，不使用第一行作为表头
        print(f"正在读取Excel文件: {excel_file}")
        df = pd.read_excel(excel_file, header=None)
        
        print(f"文件总行数: {len(df)}")
        print(f"列数: {len(df.columns)}")
        
        # 查看前几行数据来理解结构
        print(f"\n前3行数据:")
        for i in range(min(3, len(df))):
            print(f"  行{i+1}: {list(df.iloc[i])}")
        
        # 根据观察，通常通讯作者信息在后面的列
        # 让我们检查每一列，找出可能包含邮箱地址的列（通讯作者通常有邮箱）
        email_columns = []
        for col_idx in range(len(df.columns)):
            # 检查这一列是否包含邮箱地址
            sample_values = df.iloc[:10, col_idx].dropna()
            email_count = 0
            for val in sample_values:
                if isinstance(val, str) and '@' in val and '.' in val:
                    email_count += 1
            
            if email_count > 0:
                email_columns.append(col_idx)
                print(f"列{col_idx}包含{email_count}个邮箱地址")
        
        print(f"包含邮箱的列: {email_columns}")
        
        # 分析所有列，找出通讯作者相关的列
        # 通常结构是：源文章标题、文章标题、URL、作者1、邮箱1、作者2、邮箱2...
        print(f"\n分析每列的数据类型和内容:")
        for col_idx in range(min(10, len(df.columns))):  # 只看前10列
            col_data = df.iloc[:, col_idx].dropna()
            if len(col_data) > 0:
                sample = col_data.iloc[0] if len(col_data) > 0 else "空"
                print(f"  列{col_idx}: {repr(sample)}")
        
        # 假设通讯作者信息在第4列和第6列（作者姓名）
        # 我们检查所有可能的作者列（偶数列通常是姓名，奇数列是邮箱）
        author_name_columns = [3, 5]  # 第4列和第6列（0-based索引）
        
        # 找出通讯作者为空、NULL或NA的行
        empty_rows = []
        
        for idx in range(len(df)):
            # 检查所有可能的通讯作者列
            all_authors_empty = True
            
            for col_idx in author_name_columns:
                if col_idx < len(df.columns):
                    value = df.iloc[idx, col_idx]
                    
                    # 检查各种空值情况
                    is_empty = (
                        pd.isna(value) or  # NaN, None
                        value is None or
                        (isinstance(value, str) and (
                            value.strip() == '' or  # 空字符串
                            value.strip().upper() == 'NULL' or  # NULL
                            value.strip().upper() == 'NA' or  # NA
                            value.strip() == '-' or  # 横线
                            'NA' in value.upper()  # 包含NA
                        ))
                    )
                    
                    if not is_empty:
                        all_authors_empty = False
                        break
            
            if all_authors_empty:
                empty_rows.append(idx + 1)  # Excel行号从1开始
        
        print(f"\n找到 {len(empty_rows)} 行通讯作者为空/NULL/NA:")
        print(f"行号: {empty_rows}")
        
        # 格式化行号
        formatted_lines = format_line_numbers(empty_rows)
        
        # 输出到txt文件
        output_file = r"C:\Users\byan\Desktop\crawl_by_trae\crawl_by_trae\crawl\empty_authors_lines.txt"
        with open(output_file, 'w', encoding='utf-8') as f:
            f.write("通讯作者为空/NULL/NA的行号:\n\n")
            for line in formatted_lines:
                f.write(f"{line}\n")
        
        print(f"\n结果已保存到: {output_file}")
        print("格式化后的行号:")
        for line in formatted_lines:
            print(f"  {line}")
            
    except Exception as e:
        print(f"处理文件时出错: {e}")
        import traceback
        traceback.print_exc()

def format_line_numbers(numbers: List[int]) -> List[str]:
    """
    将行号列表格式化为指定格式
    - 单个行号: 5
    - 多个行号: 1,3,5
    - 范围: 1-5
    - 混合: 1,3-5,8
    """
    if not numbers:
        return []
    
    # 排序去重
    numbers = sorted(set(numbers))
    
    if len(numbers) == 1:
        return [str(numbers[0])]
    
    # 找连续范围
    ranges = []
    start = numbers[0]
    end = numbers[0]
    
    for i in range(1, len(numbers)):
        if numbers[i] == end + 1:
            # 连续
            end = numbers[i]
        else:
            # 不连续，保存当前范围
            if start == end:
                ranges.append(str(start))
            elif end == start + 1:
                ranges.append(f"{start},{end}")
            else:
                ranges.append(f"{start}-{end}")
            
            start = numbers[i]
            end = numbers[i]
    
    # 处理最后一个范围
    if start == end:
        ranges.append(str(start))
    elif end == start + 1:
        ranges.append(f"{start},{end}")
    else:
        ranges.append(f"{start}-{end}")
    
    return [",".join(ranges)]

if __name__ == "__main__":
    analyze_empty_authors()